﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using OfficeOpenXml;
using System.Web;
using System.Data.OleDb;

namespace publico
{
    public class PUB_EXCEL
    {
        public static void GerarExcel(DataTable dt,  FileInfo arquivo)
        {
           
            using (ExcelPackage xPackage = new ExcelPackage(arquivo))
            {

                xPackage.DebugMode = false;
                ExcelWorksheet worksheet = xPackage.Workbook.Worksheets.Add(arquivo.FullName.ToString());
                // Cabecalho
                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    for (int j = 0; j <= dt.Columns.Count - 1; j++)
                    {
                        worksheet.Cell(i + 1, j + 1).Value = dt.Columns[j].ToString();
                    }
                    break;
                    
                }

                // Conteudo
                for (int i = 0 ; i <= dt.Rows.Count - 1; i++)
                {
                    for (int j = 0; j <= dt.Columns.Count - 1; j++)
                    {                     
                        worksheet.Cell(i + 2, j + 1).Value = dt.Rows[i][j].ToString();
                    }
                }
                worksheet.HeaderFooter.oddHeader.CenteredText = arquivo.Name.ToString();
                // add the page number to the footer plus the total number of pages
                worksheet.HeaderFooter.oddFooter.RightAlignedText =
                    string.Format("Página {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
                // add the sheet name to the footer
                worksheet.HeaderFooter.oddFooter.CenteredText = ExcelHeaderFooter.SheetName;
                // add the file path to the footer
                //worksheet.HeaderFooter.oddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
                worksheet.HeaderFooter.oddFooter.LeftAlignedText = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString();

                // change the sheet view to show it in page layout mode
                //worksheet.View.PageLayoutView = true;
                // set some core property values
                xPackage.Workbook.Properties.Title = arquivo.Name.ToString();
                xPackage.Workbook.Properties.Author = "Softsales";
                xPackage.Save();

            }
            
        }


        public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
        {
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;
            if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            else
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
            DataSet output = new DataSet();

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                DataTable schemaTable = conn.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                foreach (DataRow schemaRow in schemaTable.Rows)
                {
                    string sheet = schemaRow["TABLE_NAME"].ToString();
                    if (!sheet.EndsWith("_"))
                    {

                        try
                        {
                            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                            cmd.CommandType = CommandType.Text;
                            DataTable outputTable = new DataTable(sheet);
                            output.Tables.Add(outputTable);
                            new OleDbDataAdapter(cmd).Fill(outputTable);
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
                        }

                    }

                }

            }

            return output;

        } 

        


    }


}
